import os
from java.lang import System, Class, ClassNotFoundException
from java.sql import DriverManager, SQLException
import sys
import string
import urllib
from CarryOn import *

from dataStructures import *
from SQLCommands import *

import csv
import ZipCodeTable
import re
from tableNameEnumWannabe import TableNames
from WebFormats import *
global stat

try:
    System.setProperty("sqlite.purejava", "true")
    Class.forName("org.sqlite.JDBC")
    con = DriverManager.getConnection("jdbc:sqlite:constellationInfo")
        
    stat = con.createStatement()
except ClassNotFoundException, e:
    sys.exit(e.message)
except SQLException, e:
    sys.exit(e.message)

def executeCommand(cmd):
    stat.execute(cmd)

def printTable(tableName):
    cmd = "SELECT * FROM " + tableName
    stat.execute(cmd)
    columns = 4
    results = stat.getResultSet()
    while results.next():
        for c in range(1, columns + 1):
            print results.getString(c), '\t',
        print

def findEverythingWithZipCode(Zip):
    listToReturn = []
    listOfConstellations = findConstellationsWithZipCode(Zip)
    listOfExoplanets = findExoplanetsWithZipCode(Zip)
    listOfNebulas = findNebulasWithZipCode(Zip)
    listToReturn.append(listOfConstellations)
    listToReturn.append(listOfExoplanets)
    listToReturn.append(listOfNebulas)
    return listToReturn

def getWordsFromWebsites(stemAddress, sites, siteWordCollection):
    bullets = []
    for site in sites:
        fullAddress = stemAddress + site
        sitewords = []
        filehandle = urllib.urlopen(fullAddress)
        lines = filehandle.readlines()
        for line in lines:
            words = re.findall("-*\w+", line)
            line = filehandle.readline()
            if not words: pass
            else:
                sitewords.extend(words)
        siteWordCollection.add(fullAddress, sitewords)
    return siteWordCollection

def getHTMLFromTitleBullets(Lines):
    HTMLInTitles = []
    bigLine = ''
    for line in Lines:
        bigLine += line
    headings = getBulletHeadings(Lines)

    titles = re.search('<h2>(.*?)</ul>', bigLine, flags = re.DOTALL)
    groupsOfTitles = titles.groups()
    return titles

def getOneBulletHeading(line):
    matches = re.match('.*?href=\".*?\"\>\s+(.*?)\s+\<.*', line)
    if matches:
        return matches.groups()
    
def getBulletHeadings(lines):
    Headings = []
    for line in lines:
        bulletHeadingsInOneLine = getOneBulletHeading(line)
        if bulletHeadingsInOneLine:
            for heading in bulletHeadingsInOneLine:
                curHeading = heading.lower()
                Headings.append(curHeading)
    return Headings

def getLinkFromLine(line):
    matches = re.match('.*?href=\"(.*?)\"\>', line)
    if matches:
        return matches.groups()[0]

def getAllLinks(website):
    Links = []
    filehandle = urllib.urlopen(website)
    line = filehandle.readline()
    while line:
        curLink = getLinkFromLine(line)
        if curLink != None:
            Links.append(curLink)
        line = filehandle.readline()
    return Links

def getBulletHeadingsFromWebsite(website):
    f = urllib.urlopen(website)
    Lines = f.readlines()
    constellations = getBulletHeadings(Lines)
    return constellations

def getTermFromSource(SourceString, termPath):
    cutDownSource = SourceString
    for i in termPath:
        beforeSyntax = i.getSyntaxBefore()
        afterSyntax = i.getSyntaxAfter()
        groups = re.search( beforeSyntax + '(.*?)' + afterSyntax, cutDownSource, flags=re.DOTALL)
        if groups != None:
            cutDownSource = groups.groups()[0]
    if cutDownSource == None:
        pass
    else:
        return cutDownSource

def getZipReaderFromFilename(Name):
    zipFile = open(Name, 'rb')
    dialect = csv.Sniffer().sniff(zipFile.read(1024))
    zipFile.seek(0)
    zipReader = csv.reader(zipFile, dialect)
    return zipReader

def setupConstellationTable():
    cmd = dropTable(TableNames.constellationTableName)
    executeCommand(cmd)
    cmd = createTable(TableNames.constellationTableName, ["name", "website", "declination", "right ascension"])
    executeCommand(cmd)
    print("here")
    
    global IndividualConstellationWordCollection
    IndividualConstellationWordCollection = carry_on_d()
    constellationStemAddress = "http://www.astro.wisc.edu/~dolan/constellations/"
    AllLinks = getAllLinks("http://www.astro.wisc.edu/~dolan/constellations/constellation_list.html")
    getWordsFromWebsites(constellationStemAddress, AllLinks, IndividualConstellationWordCollection)
    global constellations
    constellationsSite = "http://www.astro.wisc.edu/~dolan/constellations/constellation_list.html"
    constellations = getBulletHeadingsFromWebsite(constellationsSite)
    formatForConstellationsSite = setupWebFormatConstellationSite()
    for site in AllLinks:
        indiConstellationSite = constellationStemAddress + site
        filehandleForIndiSite = urllib.urlopen(indiConstellationSite)
        fullSourceForIndiSite = filehandleForIndiSite.read()
        dictionaryOfTerms = getTermsFromSite(fullSourceForIndiSite, formatForConstellationsSite)
        declination = dictionaryOfTerms["declination"]
        rightAscension = dictionaryOfTerms["rightAscension"]
        title = dictionaryOfTerms["title"]
        if len(title) > 20:
            continue
        else:
            cmd = insertToTableNonAuto(TableNames.constellationTableName, title.lower().strip(), indiConstellationSite, declination, rightAscension)
            stat.execute(cmd)
    print("done with constellations")

def setupExoplanetTable():
    cmd = dropTable(TableNames.exoplanetTableName)
    stat.execute(cmd)    
    cmd = createTable(TableNames.exoplanetTableName, ["name", "website", "declination", "right ascension"])
    executeCommand(cmd)
    formatForExoplanetSite = setupWebFormatExoplanetSite()
    
    exoplanets = getExoplanetsInformation(formatForExoplanetSite)
    for exoplanet in exoplanets:
        exName = exoplanet.getName()
        exSite = exoplanet.getSite()
        exDecl = exoplanet.getDeclination()
        exRA = exoplanet.getRightAscension()
        if len(exName) > 20 or len(exDecl) > 20 or len(exRA) > 20:
            continue
        cmd = insertToTableNonAuto(TableNames.exoplanetTableName, exName.lower().strip(), exSite, int(exDecl), int(exRA))
        executeCommand(cmd)
    print("done with exoplanets")
    
def setupNebulaTable():
    cmd = dropTable(str(TableNames.NebulaTableName))
    stat.execute(cmd)
    zipReader = getZipReaderFromFilename('LyndsBrightNebulae.csv')
    fieldNames = zipReader.next()
    fieldNames = [fieldNames[0] + ' VARCHAR(40)', 'website VARCHAR(40)', fieldNames[1] + '  DECIMAL(4,2)', fieldNames[2] + ' DECIMAL(4,2)']
    cmd = createTable(str(TableNames.NebulaTableName), fieldNames)
    executeCommand(cmd)
    for fieldValues in zipReader:
        if len(fieldValues[0]) < 1:
            continue
        else:
            rawDec = fieldValues[1]
            dec = rawDec[0:3]
            rawRa = fieldValues[2]
            ra = rawRa[0:2]
            cmd = insertToTableNonAuto(str(TableNames.NebulaTableName), fieldValues[0].lower().strip(), "http://en.wikipedia.org/wiki/List_of_planetary_nebulae", dec, ra)
            executeCommand(cmd)
    print("done with nebulas")
            
def getTermsFromSite(fullSource, websiteFormat):
    termsWeAreLookingFor = websiteFormat.getTerms()
    dictionaryOfTerms = {}
    for curTermLooking in termsWeAreLookingFor:
        curTermPath = websiteFormat.getTermpath(curTermLooking)
        returnedTerm = getTermFromSource(fullSource, curTermPath.getPathAsList())
        dictionaryOfTerms[curTermLooking] = returnedTerm
    return dictionaryOfTerms

def getLinesFromWebsite(site):
    filehandle = urllib.urlopen(site)
    lines = filehandle.read()
    return lines

def getExoplanetLinks(lines):
    allLinks = re.findall('.*?class="cellule8" nowrap>&nbsp;<a href="(.*?)" target="_blank".*?', lines)
    return allLinks

def setupExoplanet(indiSite, exoplanetNames, format):
    exoplanetSiteDomain = "http://exoplanet.eu/"
    wholeLink = exoplanetSiteDomain + indiSite
    filehandle = urllib.urlopen(wholeLink)
    fullSource = filehandle.read()
    dictionaryOfTerms = getTermsFromSite(fullSource, format)
    nameInSource = dictionaryOfTerms["title"]
    exoplanet = None
    for name in exoplanetNames:
        if name == nameInSource:
            exoplanet = solarObjectInfo(name, wholeLink, dictionaryOfTerms["declination"], dictionaryOfTerms["rightAscension"])
    return exoplanet
    
def getExoplanetNames(lines):
    exoplanets = []
    exoplanetNames = re.findall('.*?target="_blank">(.*?)</a>.*?', lines)
    if exoplanetNames != None:
        for name in exoplanetNames:
            exoplanets.append(name)
    return exoplanets

def getExoplanetsInformation(formatForIndiExoplanetSource):
    site = "http://exoplanet.eu/catalog-all.php"
    lines = getLinesFromWebsite(site)
    exoplanetsList = getExoplanetNames(lines)
    exoplanetsReturned = []
    links = getExoplanetLinks(lines)
    maxCount = 10
    count = 0
    for indiSite in links:
        exoplanet = setupExoplanet(indiSite, exoplanetsList, formatForIndiExoplanetSource)
        if exoplanet != None:
            if count > maxCount:
                break
            count += 1
            exoplanetsReturned.append(exoplanet)
    return exoplanetsReturned

def setupTheZipData():
    ZipTableName = "ZipTable"
    cmd = dropTable(ZipTableName)
    stat.execute(cmd)
    zipReader = getZipReaderFromFilename('zipCodePrimaryData.csv')
    fieldNames = zipReader.next()
    fieldNames = [fieldNames[0] + ' INT', fieldNames[1] + ' VARCHAR(40)', fieldNames[2] + ' CHAR(3)', fieldNames[3] + ' DECIMAL(4,2)', fieldNames[4] + ' DECIMAL(4,2)']
    cmd = createTable(ZipTableName, fieldNames)
    executeCommand(cmd)
    for fieldValues in zipReader:
        cmd = insertToTable(ZipTableName, fieldValues)
        executeCommand(cmd)
    printTable(ZipTableName)

def getLatitudeWithZipCode(Zip):
    latitudeOfZip = None
    cmd = selectAllWhere(TableNames.zipCodeTableName, "zipCode", Zip)
    if stat.execute(cmd):
            results = stat.getResultSet()
            latitudeOfZip = results.getString(4)
    return latitudeOfZip

def findConstellationsForNameInConstellationsTable(name):
    cmd = getSQLCommandSelectWhere(TableNames.constellationTableName, 'name', name)
    websiteList = getNamesWithcmd(cmd)
    return websiteList

def findExoplanetsForNameInExoplanetsTable(name):
    cmd = getSQLCommandSelectWhere(TableNames.exoplanetTableName, 'name', name)
    websiteList = getNamesWithcmd(cmd)
    return websiteList
            
def findNebulasForNameInNebulaTable(name):
    cmd = getSQLCommandSelectWhere(TableNames.NebulaTableName, 'name', name)
    websiteList = getNamesWithcmd(cmd)
    return websiteList

def getWebSitesWithcmd(cmd):
    returnList = []
    if stat.execute(cmd):
        results = stat.getResultSet()
        while results.next():
            CurConstellation = results.getString(2)
            returnList.append(CurConstellation)
    return returnList

def getNamesWithcmd(cmd):
    returnList = []
    if stat.execute(cmd):
        results = stat.getResultSet()
        while results.next():
            CurConstellation = results.getString(1)
            CurDeclination = results.getString(3)
            CurRightAscension = results.getString(4)
            returnList.append( [CurConstellation, CurDeclination, CurRightAscension] )
    return returnList

def findConstellationsWithZipCode(Zip):
    Latitude = getLatitudeWithZipCode(Zip)
    intLatitude = int(Latitude.split('.')[0])
    constellationsBetween = searchDatabaseForConstellationsBetweenDeclinations(intLatitude - 20, intLatitude + 20)
    return constellationsBetween

def findExoplanetsWithZipCode(Zip):
    Latitude = getLatitudeWithZipCode(Zip)
    intLatitude = int(Latitude.split('.')[0])
    exoplanetsBetween = searchDatabaseForExoplanetsBetweenDeclinations(intLatitude - 20, intLatitude + 20)
    return exoplanetsBetween

def findNebulasWithZipCode(Zip):
    Latitude = getLatitudeWithZipCode(Zip)
    intLatitude = int(Latitude.split('.')[0])
    nebulasBetween = searchDatabaseForNebulasBetweenDeclinations(intLatitude - 20, intLatitude + 20)
    return nebulasBetween

def searchDatabaseForConstellationsBetweenDeclinations(firstDeclination, secondDeclination):
    cmd = getSQLCommandSelectBetween(TableNames.constellationTableName, "declination", firstDeclination, secondDeclination)
    constellationsBetween = []
    if stat.execute(cmd):
            results = stat.getResultSet()
            while results.next():
                CurConstellation = results.getString(1)
                CurDeclination = results.getString(3)
                CurRightAscension = results.getString(4)
                constellationsBetween.append( [CurConstellation, CurDeclination, CurRightAscension] )
    return constellationsBetween

def searchDatabaseForExoplanetsBetweenDeclinations(firstDeclination, secondDeclination):
    cmd = getSQLCommandSelectBetween(TableNames.exoplanetTableName, "declination", firstDeclination, secondDeclination)
    exoplanetsBetween = []
    if stat.execute(cmd):
            results = stat.getResultSet()
            while results.next():
                CurExoplanet = results.getString(1)
                exoplanetsBetween.append(CurExoplanet)
    return exoplanetsBetween

def searchDatabaseForNebulasBetweenDeclinations(firstDeclination, secondDeclination):
    cmd = getSQLCommandSelectBetween(TableNames.NebulaTableName, "declination", firstDeclination, secondDeclination)
    nebulasBetween = []
    if stat.execute(cmd):
            results = stat.getResultSet()
            while results.next():
                CurNebula = results.getString(1)
                nebulasBetween.append(CurNebula)
    return nebulasBetween

def solarMain():
    #setupConstellationTable()
    #setupExoplanetTable()
    #setupNebulaTable()

    findConstellationsForNameInConstellationsTable("virgo")

    #printTable(TableNames.constellationTableName)
    #printTable(TableNames.exoplanetTableName)
    #printTable(TableNames.NebulaTableName)
    print("finished")
if __name__ == '__main__':
    from ConstellationGUI import guiFrame
    gui = guiFrame()
    solarMain()
    Zip = "72032"
    
    #createTable(exoplanetTableName, ["name", "website", "declination", "right ascension"])
    #getExoplanets()
    
    constellationsBetween = findConstellationsWithZipCode(Zip)
